package com.sqlutil;



import java.sql.*;



public class SQLBase {

	public static Connection c=null;	

	public static Statement stmt=null;

	

	public void CreateTable(){

	    try {

	      Class.forName("org.sqlite.JDBC");

	      c = DriverManager.getConnection("jdbc:sqlite:data/test.db");

	      System.out.println("Opened database successfully");



	      stmt = c.createStatement();

//	      String sql = "CREATE TABLE COMPANY " +

//	                   "(ID INT PRIMARY KEY     NOT NULL," +

//	                   " NAME           TEXT    NOT NULL, " + 

//	                   " AGE            INT     NOT NULL, " + 

//	                   " ADDRESS        CHAR(50), " + 

//	                   " SALARY         REAL)"; 

	      

	      String sql="create table contractor"+

	    		  	 "(id int primary	key		not null,"+

	    		  	 "name			  	text 	not null,"+

	    		  	 "title				text,"+

	    		  	 "number			int		not	null,"+

	    		  	 "address			text,"+

	    		  	 "remark			text	not null);";

	      stmt.executeUpdate(sql);

	      stmt.close();

	      c.close();

	    } catch ( Exception e ) {

	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );

	      System.exit(0);

	    }

	    System.out.println("Table created successfully");

	}



	@SuppressWarnings("null")

	public  void DeleteTbale(String tname){

	    try {

	      Class.forName("org.sqlite.JDBC");

	      c = DriverManager.getConnection("jdbc:sqlite:data/test.db");

	      System.out.println("Opened database successfully\n");

	      stmt = c.createStatement();	

	      

	      String sql="DROP TABLE if exists "+tname+";";

//	      String sql="select * frome contractor";

	      stmt.executeUpdate(sql);

	      stmt.close();

	      c.close();

	    } catch ( Exception e ) {

	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );

	      System.exit(0);

	    }

	    System.out.println("Table deleted successfully");

	}

	

	public void ConnectSQLDB(){

	    try {

	      Class.forName("org.sqlite.JDBC");

	      c = DriverManager.getConnection("jdbc:sqlite:data/mydb.db");

	      stmt = c.createStatement();	

	      System.out.println("Opened database successfully\n");

	    } catch ( Exception e ) {

	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );

	      System.exit(0);

	    }

	    System.out.println("Table connected successfully");

	}

	public void DisConnectSQLDB(){

	    try {

	      stmt.close();

	      c.close();

	    } catch ( Exception e ) {

	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );

	      System.exit(0);

	    }

	    System.out.println("Table disconnected successfully");

	}

	

	public void CreateTableContractor(){

	    try {

	      Class.forName("org.sqlite.JDBC");

	      c = DriverManager.getConnection("jdbc:sqlite:data/mydb.db");

	      System.out.println("Opened database successfully");



	      stmt = c.createStatement();

//	      String sql = "CREATE TABLE COMPANY " +

//	                   "(ID INT PRIMARY KEY     NOT NULL," +

//	                   " NAME           TEXT    NOT NULL, " + 

//	                   " AGE            INT     NOT NULL, " + 

//	                   " ADDRESS        CHAR(50), " + 

//	                   " SALARY         REAL)"; 

	      /*

	       * 

			  

			  

			  

			  

			  

			  `remark` VARCHAR(45) NULL ,

			  

			  

			

	       * */

	      

	      String sql="CREATE  TABLE IF NOT EXISTS `contractor` ("+

	    		  	 "`id` INT NOT NULL ,"+

	    		  	 "`name` VARCHAR(20) NOT NULL ,"+

	    		  	 "`phonenumber` VARCHAR(20) NOT NULL ,"+

	    		  	 "`email` VARCHAR(45) NULL ,"+

	    		  	 "`address` VARCHAR(60) NULL ,"+

	    		  	 "`remark` VARCHAR(45) NULL ,"+

	    		  	 "PRIMARY KEY (`id`))";

//	    		  	 "UNIQUE INDEX `id_UNIQUE` (`id` ASC) )"+

//	    		  	 "ENGINE = InnoDB;";

	      stmt.executeUpdate(sql);

	      stmt.close();

	      c.close();

	    } catch ( Exception e ) {

	      System.err.println( e.getClass().getName() + ": " + e.getMessage() );

	      System.exit(0);

	    }

	    System.out.println("Table created successfully");

	}



}

